/* 20251013-1543-openGauss获取表结构DDL脚本 */
drop function if exists get_table_ddl(TEXT, TEXT);
CREATE OR REPLACE FUNCTION get_table_ddl(table_name_i TEXT,input_text TEXT) 
RETURNS TEXT
AS $$
DECLARE
  result_text TEXT := '';
  lines TEXT[];
  v_create_table_scripts TEXT;
  v_index_scripts TEXT;
  v_comment_scripts TEXT;
BEGIN
  /* 1、 获取createa table语句 */
  -- 替换 SET search_path xxx; 为空
  input_text := regexp_replace(input_text, '(^|\n)SET\s+search_path\s*=\s*[^\n]*;\s*(?=\n|$)', '');
  -- RAISE NOTICE '1 - input_text=%', input_text;
  -- 中文换行符替换为英文换行符
  input_text := REGEXP_REPLACE(input_text, E'\\r\\n', E'\\n', 'g');
  -- RAISE NOTICE '2 - input_text=%', input_text;
  -- 可以高效去除字符串开头的空行。
  input_text := REGEXP_REPLACE(input_text, '^(\s*\n)+', '', 'g');
  -- 去除字符串“::regclass”。
  input_text := regexp_replace(input_text,'::regclass', '', 'gi');
  -- 去除字符串“ without time zone”。
  input_text := regexp_replace(input_text,' without time zone', '', 'gi');
  -- 将输入文本按换行符分割成数组
  lines := string_to_array(input_text, E';\n');
  -- RAISE NOTICE '2 - lines=%', lines;
  v_create_table_scripts := lines[1];
  -- 移除末行
  v_create_table_scripts := REGEXP_REPLACE(v_create_table_scripts, E'(.*?)(\r?\n[^\r\n]*)$', E'\\1', 'g') || ';';
  -- RAISE NOTICE 'v_create_table_ddl=%', v_create_table_scripts;
  
  /* 2、 获取索引创建语句 */
  begin
    select string_agg(index_def,E'\n') as index_scripts into v_index_scripts
      from (select regexp_replace(
                     regexp_replace(
                       regexp_replace(index_def
                       ,' WITH \(storage_type=USTORE\)', '', 'gi')
                     ,' TABLESPACE pg_default', '', 'gi')
                   ,' USING ubtree', '', 'gi') as index_def
              from index_info_v
             where table_name = table_name_i
               and schema = CURRENT_SCHEMA()
             order by case index_is_primary when 'Y' then 1 else 2 end
                     ,index_name) t
    ;
  EXCEPTION
    WHEN NO_DATA_FOUND THEN
      -- RAISE NOTICE '捕获异常：没有数据返回';
      v_index_scripts := '';
  end;
  -- RAISE NOTICE 'v_index_scripts=%', v_index_scripts;
  
  /* 3、 获取注释创建语句 */
  begin
    select string_agg(comments, E'\n') as comment_scripts into v_comment_scripts
      from (select 'COMMENT ON TABLE  ' || table_name || ' IS ''' || REPLACE(comments, '''', '''''') || ''';' as comments
              from my_tab_comments 
             where table_name = table_name_i
               and schema = CURRENT_SCHEMA()
            union all 
            (select 'COMMENT ON COLUMN ' || cc.table_name || '.' || cc.column_name || 
                   ' IS ''' || REPLACE(cc.comments, '''', '''''') || ''';'
              from my_tab_columns tc
                  ,my_col_comments cc
             where tc.schema = cc.schema
               and tc.table_name = cc.table_name
               and tc.column_name = cc.column_name
               and tc.schema = CURRENT_SCHEMA()
               AND tc.table_name = table_name_i
             order by tc.schema, tc.table_name, tc.column_id)) t
    ;
  EXCEPTION
    WHEN NO_DATA_FOUND THEN
      -- RAISE NOTICE '捕获异常：没有数据返回';
      v_comment_scripts := '';
  end;
  
  result_text := 'DROP TABLE IF EXISTS ' || table_name_i || ' PURGE;' || E'\n' || v_create_table_scripts || E'\n' || v_index_scripts || E'\n' || v_comment_scripts || E'\n';
  RETURN result_text;
END;
$$ LANGUAGE plpgsql;



